# coding:utf-8
"""
    @author : 计祝兵
    @date : 2024/6/5 10:59
    @file : write.py
    @desc : 写入Excel
"""

import xlsxwriter
import xlrd


# 读取Excel文件内容
def read():
    result = []
    excel = xlrd.open_workbook('study.xlsx')
    book = excel.sheet_by_name('学生手册')
    for i in book.get_rows():
        content = []
        for j in i:
            content.append(j.value)
        result.append(content)
    return result


# 向文件中写入数据
def write(content):
    excel = xlsxwriter.Workbook('Write.xlsx')
    books = excel.add_worksheet('学生手册')

    for index, data in enumerate(content):
        for sub_index, sub_data in enumerate(data):
            books.write(index, sub_index, sub_data)

    books1 = excel.add_worksheet('学生等级')
    data = [
        ['优秀', '良好', '中', '差'],
        [1100, 2000, 900, 1000]
    ]
    books1.write_column('A1', data[0])
    books1.write_column('B1', data[1])

    chart = excel.add_chart({'type': 'pie'})
    chart.add_series({
        'categories': '=学生等级!$A1:$A4',
        'values': '=学生等级!$B1:$B4',
        'name': '成绩占比'
    })

    chart.set_title({'name': '成绩占比图标'})
    books1.insert_chart('A10', chart)

    excel.close()


if __name__ == "__main__":
    result = read()
    write(result)
